package com.sl.au.repository;
import com.sl.au.entity.ReplaceDevicePrice;
import com.sl.au.entity.SinglePump;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly = true)
public interface ReplaceDevicePriceRepository extends JpaRepository<ReplaceDevicePrice, String> {
	/*
	 * 得到备品记录，分部门
	 */
    @Query(" select r from ReplaceDevicePrice r where r.superior =?1 and (r.reDeviceName like '%'+?2+'%'"
    		+ "or r.replacementDevice.type like '%'+?2+'%' or r.replacementDevice.material like '%'+?2+'%')")
    Page<ReplaceDevicePrice> getReplace(String superior, String key, Pageable pageable);
    
    /**
     * 得到数据条数
     */
    @Query("select count(r) from ReplaceDevicePrice r where r.superior =?1 "
    		+ "and (r.reDeviceName like '%'+?2+'%' "
    		+ "or r.replacementDevice.type like '%'+?2+'%' or r.replacementDevice.material like '%'+?2+'%')")
    long getCount(String superior, String key);
    
    @Query(" select r from ReplaceDevicePrice r where r.reDeviceName like '%'+?1+'%' "
    		+ "or r.replacementDevice.type like '%'+?1+'%' "
    		+ "or r.replacementDevice.material like '%'+?1+'%'")
    Page<ReplaceDevicePrice> getReplace(String key, Pageable pageable);
    
    @Query("select count(r) from ReplaceDevicePrice r where r.reDeviceName like '%'+?1+'%'"
    		+ "or r.replacementDevice.type like '%'+?1+'%' "
    		+ "or r.replacementDevice.material like '%'+?1+'%'")
    long getCount(String key);
    
    /*
	 * 删除与备件ID相关的价格
	 */
	@Transactional
	@Modifying  
	@Query("delete from ReplaceDevicePrice r where r.replacementDevice.id=?1 ")
	public void deleteDevicePrice(String replaceId);
	

	/*
	 * 通过部门和备件名称找到价格
	 */
	@Query("select r from ReplaceDevicePrice r where r.replacementDevice.deviceName=?1 and "
			+ "r.replacementDevice.type=?2 and r.replacementDevice.material=?3 and r.superior=?4")
	ReplaceDevicePrice findByNameAndSuperior(String deviceName,String type,String material,String superior);

   
}